package demo1;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

public class Test_phone {

    @Test
    public void cheshi() {
        System.out.println("欢迎来到我的电话本~");
        System.out.println("1、查看所有联系人");
        System.out.println("2、查看某个联系人详情，需要输入手机号码");
        System.out.println("3、修改某个联系人  可以修改名字和性别");
        System.out.println("4、删除联系人");
        Scanner sc = new Scanner(System.in);
        Test_phone tp = new Test_phone();

        while (true) {
            switch (sc.nextInt()){
                case 1:
                    try {
                        System.out.println("所有的信息如下：");
                        tp.dataBook();
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    break;
                case 2:
                    try {
                        System.out.println("请您输入想要添加的 id,phone,name,sex,age");
                        dataAdd(sc.next(),sc.next(),sc.next(),sc.nextInt());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    break;
                case 3:
                    try {
                        System.out.println("输入想修改的字段，不用输入原来的名字性别，直接输入修改后的名字，修改后的性别");
                        tp.dataUpdate(sc.nextInt(),sc.next(),sc.next());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    break;
                case 4:
                    try {
                        System.out.println("请输入要删除的字段id");
                        tp.dataDel(sc.nextInt());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    break;
                case 5:
                    System.out.println("已退出~~");
                    return;


            }
        }

    }

    Connection connection ;
    PreparedStatement preparedStatement;

    @Before
    public void init() throws Exception {
        System.out.println("所有test方法执行前，必须先执行我");
        Class.forName("com.mysql.jdbc.Driver"); // 可以不写
        connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/text01",
                "root",
                "123456");
    }



    public void dataAdd(String phone, String name, String sex, int age) throws Exception {
        String sql = "insert into student02 values(null,?,?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1,phone);
        preparedStatement.setString(2,name);
        preparedStatement.setString(3,sex);
        preparedStatement.setInt(4,age);

        preparedStatement.execute();
        preparedStatement.close();
    }

    public void dataUpdate(int id, String name, String sex) throws Exception{
        String sql = "update student02 set name='小王' where id = ? ";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.setString(1,name);
        preparedStatement.setString(1,sex);
        preparedStatement.execute();

    }

    // 删除指定id的数据
    public void dataDel(int id) throws Exception{
        String sql = "delete from student02 where id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,id);
        preparedStatement.execute();
    }

    // 查看所有数据
    public void  dataBook() throws Exception{
        String sql ="select * from student02";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String phone = resultSet.getString("phone");
            String name = resultSet.getString("name");
            int sex = resultSet.getInt("sex");
            int age = resultSet.getInt("age");
            System.out.println(id+","+phone+","+name+","+ sex + "," + age);
        }

        // 记得用完之后需要关闭，否则，将来会内存溢出的
        resultSet.close();
    }

    @After
    public void destory() throws Exception {
        System.out.println("所有test方法执行后，必须执行我");

        preparedStatement.close();
        connection.close();
    }

}
